package com.hysoft.service.Impl;

import com.hysoft.service.OutDataFromOracleToAccess;
import com.hysoft.util.LogUtil;
import org.springframework.stereotype.Service;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Service
public class OutDataFromOracleToAccessImpl implements OutDataFromOracleToAccess {

    /**
     * 创建表并且插入数据
     *
     * @param oracleConn
     * @param accessConn
     * @param owner
     * @throws SQLException
     */
    @Override
    public void createTable(String tableName, Connection oracleConn, Connection accessConn, String owner, String ConditionSql) throws SQLException {

        //String tableName = "";
        //PreparedStatement accessAllTableNamePstmt = null;
        //PreparedStatement accessAllTablePstmt = null;
        //ResultSet accessAllTableNameResultSet = null;
        PreparedStatement createAsseccTablePstmt = null;
        ResultSet accessTableNameResult = null;
        PreparedStatement pst = null;
        ResultSet oracelResultSet = null;
        ResultSet accessConnMetaDataTablesrs = null;
        try {

            /**
             * 获取oracle数据库中的表的数据
             */
//            String oracleAllTableNameSql = "select * from "+owner+"."+tableName;
//            oralceAllTableNamePstmt = oracleConn.prepareStatement(oracleAllTableNameSql);
//            oracelAllTableResultSet = oralceAllTableNamePstmt.executeQuery();

            /**
             * 遍历oracle中的表
             */
            //while(oracelAllTableResultSet.next()) {
            int accessAllTableNameCount = 0;
            int count = 0;
            /**
             * 获取access数据库中的所有表
             */
            String[] str = {"TABLE"};
            DatabaseMetaData accessAllTableNamedbmd = accessConn.getMetaData();
            accessTableNameResult = accessAllTableNamedbmd.getTables(null, null, "%", str);
//            String accessAllTableNameSql = "SELECT name FROM MSYSOBJECTS where type=1 and flags=0";
//            accessAllTableNamePstmt = accessConn.prepareStatement(accessAllTableNameSql);
//            accessAllTableResultSet = accessAllTableNamePstmt.executeQuery();
            while (accessTableNameResult.next()) {
                accessAllTableNameCount++;
            }
            accessTableNameResult.close();
            /**
             * 获取oracle表的字段，类型
             */
            String sql = "select * from " + owner + "." + tableName;
            pst = oracleConn.prepareStatement(sql);
            oracelResultSet = pst.executeQuery();
            ResultSetMetaData resultSetMetaData = oracelResultSet.getMetaData();
            /**
             * 遍历access数据库中的所有表
             */
//            accessAllTablePstmt = accessConn.prepareStatement(accessAllTableNameSql);
//            accessAllTableNameResultSet = accessAllTablePstmt.executeQuery();
            DatabaseMetaData accessConnMetaData = accessConn.getMetaData();
            accessConnMetaDataTablesrs = accessConnMetaData.getTables(null, null, "%", str);
            while (accessConnMetaDataTablesrs.next()) {
                if (!tableName.equalsIgnoreCase(accessConnMetaDataTablesrs.getString(3))) {
                    count++;
                    continue;
                } else {
                    insertDate(accessConn, oracleConn, tableName, owner, ConditionSql);
                    break;
                }
            }
            if (count == accessAllTableNameCount) {
                String col = null;
                for (int j = 1; j <= resultSetMetaData.getColumnCount(); j++) {
                    if ("VARCHAR2".equalsIgnoreCase(resultSetMetaData.getColumnTypeName(j)) || "CHAR".equalsIgnoreCase(resultSetMetaData.getColumnTypeName(j))) {
                        col += resultSetMetaData.getColumnLabel(j) + " " + "VarChar" + ",";
                    } else if ("DATE".equalsIgnoreCase(resultSetMetaData.getColumnTypeName(j))) {
                        col += resultSetMetaData.getColumnLabel(j) + " " + "DATE" + ",";
                    } else if ("NUMBER".equalsIgnoreCase(resultSetMetaData.getColumnTypeName(j))) {
                        col += resultSetMetaData.getColumnLabel(j) + " " + "NUMBER" + ",";
                    }

                }
                /**
                 * 截取字段的字符串
                 */
                String paramStr = col.substring(4, col.length() - 1);
                /**
                 * 建表语句
                 */
                String createTableSql = "create table" + "  " + tableName + "(" + paramStr + ")";
                createAsseccTablePstmt = accessConn.prepareStatement(createTableSql);
                Integer success = createAsseccTablePstmt.executeUpdate();
                if (success == -1) {
                    System.out.println("创建成功");
                    insertDate(accessConn, oracleConn, tableName, owner, ConditionSql);
                    createAsseccTablePstmt.close();
                } else {
                    createAsseccTablePstmt.close();
                }
            }
            //     }

        } catch (Exception e) {
            try {
                LogUtil.inserCorrectLog("Out",e.toString(),"error.txt");
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }


        if (createAsseccTablePstmt != null) {
            createAsseccTablePstmt.close();
        }
        if (pst != null) {
            pst.close();
        }
        if (oracelResultSet != null) {
            oracelResultSet.close();
        }
        if (accessConnMetaDataTablesrs != null) {
            accessConnMetaDataTablesrs.close();
        }
        if (accessConn != null) {
            accessConn.close();
        }
        if (oracleConn != null) {
            oracleConn.close();
        }
    }

    @Override
    public void condition(String SJB, String conditionSql, Connection accessConn, Connection oracleConn, String owner) {
        //access连接
        Connection accessConnetion = null;
        PreparedStatement accessPstm = null;


        //oracle连接
        Connection oracleConnection = null;
        PreparedStatement oraclePstm = null;
        ResultSet oralceRs = null;
        ResultSetMetaData oracleRSMD = null;

        try {
            oracleConnection = oracleConn;
            oraclePstm = oracleConnection.prepareStatement(conditionSql);
            oralceRs = oraclePstm.executeQuery();
            oracleRSMD = oralceRs.getMetaData();

            //判断条件查询的sql语句是否查到了东西，没有的话直接写入日志，提醒修改条件
            if (oralceRs.next()==false){
                String tmpInfo = "【出错信息】oralceRs.next()==false\n";
                tmpInfo +=LogUtil.conditionExport(SJB,conditionSql);
                LogUtil.inserCorrectLog("Out",tmpInfo,"error.txt");
            }
            String[] str = {"TABLE"};
            boolean createTable = true; //缺省值：创建表
            accessConnetion = accessConn;
            DatabaseMetaData accessDbmd = accessConnetion.getMetaData();
            ResultSet accrs = accessDbmd.getTables(null, null, "%", str);

            while (accrs.next()) {
                if (SJB.equalsIgnoreCase(accrs.getString(3))) {
                    createTable = false;    //为否不需要创建
                }
            }
            //条件导出异常标志位 0正常 1异常
            int flag = 0;
            //如果不存在先创建再插入，否则直接插入
            if (createTable) {
                String col = null;
                for (int j = 1; j <= oracleRSMD.getColumnCount(); j++) {
                    if ("VARCHAR2".equalsIgnoreCase(oracleRSMD.getColumnTypeName(j)) || "CHAR".equalsIgnoreCase(oracleRSMD.getColumnTypeName(j))) {
                        col += oracleRSMD.getColumnLabel(j) + " " + "VarChar" + ",";
                    } else if ("DATE".equalsIgnoreCase(oracleRSMD.getColumnTypeName(j))) {
                        col += oracleRSMD.getColumnLabel(j) + " " + "DATE" + ",";
                    } else if ("NUMBER".equalsIgnoreCase(oracleRSMD.getColumnTypeName(j))) {
                        col += oracleRSMD.getColumnLabel(j) + " " + "NUMBER" + ",";
                    }

                }
                String paramStr = col.substring(4, col.length() - 1);
                String createTableSql = "create table" + "  " + SJB + "(" + paramStr + ")";
                accessPstm = accessConnetion.prepareStatement(createTableSql);
                Integer success = accessPstm.executeUpdate();

                if (success == -1) {
                    System.out.println("创建成功");
                    flag = insertDate(accessConnetion, oracleConnection, SJB, owner, conditionSql);
                }
            } else {
                flag = insertDate(accessConnetion, oracleConnection, SJB, owner, conditionSql);
            }
            if(flag == 1){
                LogUtil.inserCorrectLog("Out",LogUtil.conditionExport(SJB,conditionSql),"error.txt");
            }
            oralceRs.close();
            oraclePstm.close();
            accrs.close();
        } catch (SQLException e) {
            try {
//                String tmpInfo = LogUtil.conditionExport(SJB,conditionSql);
//                tmpInfo+=LogUtil.InfoWithException(e);
                LogUtil.inserCorrectLog("Out",e.toString(),"error.txt");
                System.out.println(e);
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 查找并返回库中的所有数据表
     *
     * @param owner 数据库名称
     * @return 包含所有表名的list集合
     */
    @Override
    public List<String> allTables(Connection oracleConn, String owner) {
        Connection connection = oracleConn;
        PreparedStatement oralceAllTableNamePstmt = null;
        ResultSet oracelAllTableResultSet = null;
        String oracleAllTableNameSql = "select * from all_tables where owner=" + "'" + owner + "'";
        List<String> allTableNames = new ArrayList<>();
        try {
            oralceAllTableNamePstmt = connection.prepareStatement(oracleAllTableNameSql);
            oracelAllTableResultSet = oralceAllTableNamePstmt.executeQuery();

            while (oracelAllTableResultSet.next()) {
                allTableNames.add(oracelAllTableResultSet.getString(2));
            }
            if (oracelAllTableResultSet != null) {
                oracelAllTableResultSet.close();
            }
            if (oralceAllTableNamePstmt != null) {
                oralceAllTableNamePstmt.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            try {
                LogUtil.inserCorrectLog("Out",e.toString(),"error.txt");
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
        return allTableNames;
    }

    /**
     * 查找并返回选中表中的所有字段
     *
     * @param SJB 被选中的表
     * @return 选中表中的所有字段
     */
    @Override
    public List<String> allFields(String SJB, Connection oracleConn) {
        Connection connection = oracleConn;
        PreparedStatement allFieldsPstm = null;
        ResultSet allFieldsRs = null;
        ResultSetMetaData metaData = null;
        String getAllFieldsSql = "select * from " + SJB;
        List<String> allFields = new ArrayList<>();
        try {
            allFieldsPstm = connection.prepareStatement(getAllFieldsSql);
            allFieldsRs = allFieldsPstm.executeQuery();
            metaData = allFieldsRs.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                allFields.add(metaData.getColumnName(i));
            }
            if (allFieldsRs != null) {
                allFieldsRs.close();
            }
            if (allFieldsPstm != null) {
                allFieldsPstm.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            try {
                LogUtil.inserCorrectLog("Out",e.toString(),"error.txt");
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
        return allFields;
    }

    /**
     * @param accessConn
     * @param oracleConn
     * @param tableName
     * @param owner
     */
    @Override
    public int insertDate(Connection accessConn, Connection oracleConn, String tableName, String owner, String ConditionSql) {

        //条件导出异常标志位，0表示条件导出正常，1表示异常
        int flag = 0;

        PreparedStatement primaryKeyPstmt = null;
        ResultSet primaryKeyResultSet = null;
        PreparedStatement primaryKeyPstmtCount = null;
        ResultSet primaryKeyCountResultSet = null;
        PreparedStatement getPrimaryKeyPstmt = null;
        ResultSet getPrimaryKeyPstmtResultSet = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        PreparedStatement accessYesOrNoExitPstmt = null;
        // ResultSet YesOrNoExitResultSet = null;

        /**
         *获取Oracle数据库中的主键
         */
        Integer count = 0;
        String sqlPrimaryKey = "select c.column_name from all_constraints a,all_cons_columns c where  a.constraint_name=c.constraint_name(+) and a.owner=c.owner(+) and  a.constraint_type='P' and a.TABLE_NAME=" + "'" + tableName + "'" + "and a.owner=" + "'" + owner + "'" + " order by position";
        try {
            primaryKeyPstmt = oracleConn.prepareStatement(sqlPrimaryKey);

            primaryKeyResultSet = primaryKeyPstmt.executeQuery();
            /**
             * 获取主键数量
             */
            String sqlPrimaryKeyCount = "select count(c.column_name) from all_constraints a,all_cons_columns c where  a.constraint_name=c.constraint_name(+) and a.owner=c.owner(+) and  a.constraint_type='P' and a.TABLE_NAME=" + "'" + tableName + "'" + "and a.owner=" + "'" + owner + "'" + " order by position";
            primaryKeyPstmtCount = oracleConn.prepareStatement(sqlPrimaryKeyCount);
            primaryKeyCountResultSet = primaryKeyPstmtCount.executeQuery();
            while (primaryKeyCountResultSet.next()) {
                count = primaryKeyCountResultSet.getInt(1);
            }
            primaryKeyPstmtCount.close();
            primaryKeyCountResultSet.close();
            /**
             * 使用数据存储主键名称和主键类型
             */
            String primaryKeyStr[] = new String[count];
            String primaryKeyType[] = new String[count];
            int mid = 0;
            int typeMid = 0;
            /**
             * 获取主键字段加入数组
             */
            while (primaryKeyResultSet.next()) {
                primaryKeyStr[mid++] = primaryKeyResultSet.getString(1).toString();
            }
            primaryKeyPstmt.close();
            primaryKeyResultSet.close();
            /**
             * 获取主键类型
             */
            for (int i = 0; i < count; i++) {
                String getPrimaryKey = "select data_type  from all_tab_columns ut where ut.table_name=" + "'" + tableName + "'" + "and owner=" + "'" + owner + "'" + " and column_name=" + "'" + primaryKeyStr[i] + "'";
                getPrimaryKeyPstmt = oracleConn.prepareStatement(getPrimaryKey);
                getPrimaryKeyPstmtResultSet = getPrimaryKeyPstmt.executeQuery();
                while (getPrimaryKeyPstmtResultSet.next()) {
                    primaryKeyType[typeMid++] = getPrimaryKeyPstmtResultSet.getString(1).toString();
                }
                getPrimaryKeyPstmt.close();
                getPrimaryKeyPstmtResultSet.close();
            }
            /**
             * 遍历Oracle数据库中的所有数据
             */
            if (ConditionSql != null && ConditionSql != "") {
                pstmt = oracleConn.prepareStatement(ConditionSql);
                resultSet = pstmt.executeQuery();
            } else {
                String sql = "select * from " + tableName;
                pstmt = oracleConn.prepareStatement(sql);
                resultSet = pstmt.executeQuery();
            }


            while (resultSet.next()) {
                String strPrimary = "";
                for (int m = 0; m < count; m++) {
                    strPrimary += resultSet.getString(primaryKeyStr[m]) + ",";
                }
                /**
                 * 将字段分割放入数组
                 */
                String newStr[] = strPrimary.substring(0, strPrimary.length() - 1).split(",");

                String param = "";
                String value = "";
                String parames = "";
                /**
                 * 拼接sql去access数据库中查找是否存在数据
                 */
                String newParam = "";
                for (int j = 0; j < count; j++) {
                    if (primaryKeyType[j].equals("NUMBER")) {
                        parames += primaryKeyStr[j] + "=" + newStr[j] + " and" + " ";
                    } else if (primaryKeyType[j].equals("DATE")) {
                        String str = newStr[j].toString();
                        String str1 = str.substring(0, 10);
                        parames += primaryKeyStr[j] + "=" + str1 + " and" + " ";
                    } else {
                        parames += primaryKeyStr[j] + "=" + "'" + newStr[j] + "'" + " and" + " ";
                    }
                }
                newParam = parames.substring(0, parames.length() - 4);
                /**
                 * 查询数据是否存在
                 */
                boolean isOrNot = yesOrNotExitDate(tableName, newParam, accessConn);
                /**
                 * 如果没有数据，则插入数据
                 */
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                while (isOrNot) {
                    /**
                     * 遍历oracle的表，拼接字段和数据
                     */
                    for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                        param += resultSetMetaData.getColumnLabel(i) + ",";
                        if (resultSetMetaData.getColumnTypeName(i).equalsIgnoreCase("DATE")) {
                            if (resultSet.getString(i) == null) {
                                String str1 = resultSet.getString(i);
                                str1 = null;
                                value += str1 + ",";
                            } else {
                                String str = resultSet.getString(i).toString();
                                String str1 = str.substring(0, 10);
                                value += "'" + str1 + "'" + ",";
                            }

                        } else if (resultSetMetaData.getColumnTypeName(i).equalsIgnoreCase("NUMBER")) {
                            if (resultSet.getString(i) == null) {
                                Integer strNull = resultSet.getInt(i);
                                strNull = null;
                                value += strNull + ",";
                            } else {
                                value += resultSet.getString(i) + ",";
                            }
                        } else {
                            if (resultSet.getString(i) == null) {
                                String strNull = resultSet.getString(i);
                                strNull = " ";
                                value += "'" + strNull + "'" + ",";
                            } else {
                                value += "'" + resultSet.getString(i) + "'" + ",";
                            }
                        }
                    }
                    /**
                     * 截取字符串
                     */
                    String paramStr = param.substring(0, param.length() - 1);
                    String valueStr = value.substring(0, value.length() - 1);
                    /**
                     * 插入数据
                     */
                    String insertDateSql = "insert into" + " " + tableName + " " + "(" + paramStr + ")" + "values(" + valueStr + ") ";
                    PreparedStatement insertDatePstmt = accessConn.prepareStatement(insertDateSql);
                    Integer success = insertDatePstmt.executeUpdate();

                    if (success > 0) {
                        try {
                            LogUtil.inserCorrectLog("Out", LogUtil.InfoWithSuccess("access",tableName,paramStr,valueStr), "ok.txt");
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                        System.out.println("插入成功");
                        insertDatePstmt.close();
                    } else {
                        try {
                            LogUtil.inserCorrectLog("Out", LogUtil.InfoWithError("access", tableName, paramStr,valueStr), "error.txt");
                            //导出异常，标志位置1
                            flag = 1;
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                        insertDatePstmt.close();
                    }
                    break;
                }
            }
            resultSet.close();
            pstmt.close();
            if (primaryKeyPstmt != null) {
                primaryKeyPstmt.close();

            }
            if (primaryKeyResultSet != null) {
                primaryKeyResultSet.close();

            }
            if (primaryKeyPstmtCount != null) {
                primaryKeyPstmtCount.close();

            }
            if (primaryKeyCountResultSet != null) {
                primaryKeyCountResultSet.close();

            }
            if (getPrimaryKeyPstmt != null) {
                getPrimaryKeyPstmt.close();

            }
            if (getPrimaryKeyPstmtResultSet != null) {
                getPrimaryKeyPstmtResultSet.close();

            }
            if (pstmt != null) {
                pstmt.close();

            }
            if (resultSet != null) {
                resultSet.close();

            }
            if (accessYesOrNoExitPstmt != null) {
                accessYesOrNoExitPstmt.close();

            }
        } catch (SQLException e) {
            try {
                LogUtil.inserCorrectLog("Out",e.toString(),"error.txt");
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
        return flag;
    }

    @Override
    public boolean yesOrNotExitDate(String tableName, String newParam, Connection accessConn) throws SQLException {
        PreparedStatement accessYesOrNoExitPstmt = null;
        ResultSet YesOrNoExitResultSet = null;
        /**
         * 查询数据是否存在
         */
        String sqlYesOrNoExit = "select * from" + " " + tableName + " " + "where" + " " + newParam;
        accessYesOrNoExitPstmt = accessConn.prepareStatement(sqlYesOrNoExit);
        YesOrNoExitResultSet = accessYesOrNoExitPstmt.executeQuery();
        if (YesOrNoExitResultSet.next()) {
            if (accessYesOrNoExitPstmt != null) {
                accessYesOrNoExitPstmt.close();
            }
            return false;
        } else {
            if (accessYesOrNoExitPstmt != null) {
                accessYesOrNoExitPstmt.close();
            }
            return true;
        }

    }
}
